"""商品模型"""
from app.database import get_db_connection

class Product:
    """商品模型类"""
    
    @staticmethod
    def get_by_barcode(barcode):
        """根据条码获取商品"""
        connection = None
        cursor = None
        try:
            connection = get_db_connection()
            cursor = connection.cursor()
            cursor.execute(
                "SELECT barcode, name, price, unit, stock, expiry_date FROM products WHERE barcode = %s",
                (barcode,)
            )
            return cursor.fetchone()
        finally:
            if cursor:
                cursor.close()
            if connection:
                connection.close()
    
    @staticmethod
    def search(keyword):
        """搜索商品"""
        connection = None
        cursor = None
        try:
            connection = get_db_connection()
            cursor = connection.cursor()
            
            if keyword.isdigit():
                cursor.execute(
                    "SELECT barcode, name, price, unit, stock, expiry_date FROM products WHERE barcode LIKE %s LIMIT 50",
                    (f"{keyword}%",)
                )
            else:
                cursor.execute(
                    "SELECT barcode, name, price, unit, stock, expiry_date FROM products WHERE name LIKE %s LIMIT 50",
                    (f"%{keyword}%",)
                )
            
            return cursor.fetchall()
        finally:
            if cursor:
                cursor.close()
            if connection:
                connection.close()
    
    @staticmethod
    def create(barcode, name, price, unit, stock, expiry_date=None):
        """创建商品"""
        connection = None
        cursor = None
        try:
            connection = get_db_connection()
            cursor = connection.cursor()
            cursor.execute(
                "INSERT INTO products (barcode, name, price, unit, stock, expiry_date) VALUES (%s, %s, %s, %s, %s, %s)",
                (barcode, name, price, unit, stock, expiry_date)
            )
            connection.commit()
            return True
        except Exception as e:
            if connection:
                connection.rollback()
            raise e
        finally:
            if cursor:
                cursor.close()
            if connection:
                connection.close()
    
    @staticmethod
    def update(barcode, **kwargs):
        """更新商品"""
        connection = None
        cursor = None
        try:
            connection = get_db_connection()
            cursor = connection.cursor()
            
            update_fields = []
            params = []
            
            if 'name' in kwargs:
                update_fields.append("name = %s")
                params.append(kwargs['name'])
            if 'price' in kwargs:
                update_fields.append("price = %s")
                params.append(kwargs['price'])
            if 'unit' in kwargs:
                update_fields.append("unit = %s")
                params.append(kwargs['unit'])
            if 'stock' in kwargs:
                update_fields.append("stock = %s")
                params.append(kwargs['stock'])
            if 'expiry_date' in kwargs:
                update_fields.append("expiry_date = %s")
                params.append(kwargs['expiry_date'])
            
            params.append(barcode)
            cursor.execute(
                f"UPDATE products SET {', '.join(update_fields)} WHERE barcode = %s",
                params
            )
            connection.commit()
            return True
        except Exception as e:
            if connection:
                connection.rollback()
            raise e
        finally:
            if cursor:
                cursor.close()
            if connection:
                connection.close()
    
    @staticmethod
    def delete(barcode):
        """删除商品"""
        connection = None
        cursor = None
        try:
            connection = get_db_connection()
            cursor = connection.cursor()
            affected_rows = cursor.execute(
                "DELETE FROM products WHERE barcode = %s",
                (barcode,)
            )
            connection.commit()
            return affected_rows > 0
        except Exception as e:
            if connection:
                connection.rollback()
            raise e
        finally:
            if cursor:
                cursor.close()
            if connection:
                connection.close()
    
    @staticmethod
    def get_stock_warnings(page=1, page_size=10):
        """
        获取库存警告商品（支持分页）
        - 提取库存小于10个的商品
        - 零库存商品排在最前面
        - 添加is_zero_stock字段标识零库存商品
        """
        connection = None
        cursor = None
        
        try:
            # 确保page和page_size为整数
            page = int(page)
            page_size = int(page_size)
            if page < 1:
                page = 1
            if page_size < 1 or page_size > 100:
                page_size = 10
            
            connection = get_db_connection()
            cursor = connection.cursor()
            
            # 计算偏移量
            offset = (page - 1) * page_size
            
            # 查询商品数据 - 库存小于10个的商品
            # 添加is_zero_stock字段来标识商品是否为零库存
            query = """
            SELECT barcode, name, price, unit, stock, expiry_date, 
                   CASE WHEN stock = 0 THEN 1 ELSE 0 END as is_zero_stock 
            FROM products 
            WHERE stock < 10 
            ORDER BY is_zero_stock DESC, stock ASC 
            LIMIT %s OFFSET %s
            """
            cursor.execute(query, (page_size, offset))
            products = cursor.fetchall()
            
            # 查询总数
            count_query = "SELECT COUNT(*) FROM products WHERE stock < 10"
            cursor.execute(count_query)
            total_row = cursor.fetchone()
            # 安全地获取总数
            if total_row is None:
                total = 0
            elif isinstance(total_row, (list, tuple)) and len(total_row) > 0:
                total = total_row[0]
            else:
                try:
                    # 尝试直接转换
                    total = int(total_row)
                except:
                    total = 0
            
            # 计算总页数
            total_pages = (total + page_size - 1) // page_size
            
            return {
                'products': products,
                'total': total,
                'page': page,
                'page_size': page_size,
                'total_pages': total_pages
            }
        finally:
            if cursor:
                cursor.close()
            if connection:
                connection.close()
    
    @staticmethod
    def get_expiry_warnings(page=1, page_size=10):
        """
        获取近效期警告商品（支持分页）
        """
        connection = None
        cursor = None
        
        try:
            # 确保page和page_size为整数
            page = int(page)
            page_size = int(page_size)
            if page < 1:
                page = 1
            if page_size < 1 or page_size > 100:
                page_size = 10
            
            connection = get_db_connection()
            cursor = connection.cursor()
            
            # 计算偏移量
            offset = (page - 1) * page_size
            
            # 查询商品数据 - 包含已过期和即将过期的商品
            # 添加is_expired字段来标识商品是否已过期，方便前端用红色字体突出显示
            query = """
            SELECT barcode, name, price, unit, stock, expiry_date, 
                   CASE WHEN expiry_date < NOW() THEN 1 ELSE 0 END as is_expired 
            FROM products 
            WHERE expiry_date IS NOT NULL 
            AND (expiry_date < NOW() OR expiry_date <= DATE_ADD(NOW(), INTERVAL 1 MONTH)) 
            ORDER BY is_expired DESC, expiry_date ASC 
            LIMIT %s OFFSET %s
            """
            cursor.execute(query, (page_size, offset))
            products = cursor.fetchall()
            
            # 查询总数
            count_query = """
            SELECT COUNT(*) FROM products 
            WHERE expiry_date IS NOT NULL 
            AND (expiry_date < NOW() OR expiry_date <= DATE_ADD(NOW(), INTERVAL 1 MONTH))
            """
            cursor.execute(count_query)
            total_row = cursor.fetchone()
            # 安全地获取总数
            if total_row is None:
                total = 0
            elif isinstance(total_row, (list, tuple)) and len(total_row) > 0:
                total = total_row[0]
            elif isinstance(total_row, dict):
                # 尝试获取第一个值，无论键是什么
                total = list(total_row.values())[0] if total_row else 0
            else:
                try:
                    # 最后尝试直接转换
                    total = int(total_row)
                except:
                    total = 0
            
            # 计算总页数
            total_pages = (total + page_size - 1) // page_size
            
            return {
                'products': products,
                'total': total,
                'page': page,
                'page_size': page_size,
                'total_pages': total_pages
            }
        except Exception as e:
            # 记录详细的错误信息
            print(f"数据库操作错误: {str(e)}")
            import traceback
            traceback.print_exc()
            # 向上抛出异常，让路由处理
            raise
        finally:
            # 关闭数据库连接和游标
            if cursor:
                try:
                    cursor.close()
                except:
                    pass
            if connection:
                try:
                    connection.close()
                except:
                    pass

